import tkinter as tk
from tkinter import ttk, messagebox
import redis
import matplotlib.pyplot as plt
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg

class StatisticsQuery:
    def __init__(self, parent):
        self.parent = parent
        self.create_widgets()
        self.canvas = None  # Initialize canvas attribute

    def create_widgets(self):
        self.frame = tk.Frame(self.parent)
        self.frame.pack(fill=tk.BOTH, expand=True)

        self.button_frame = tk.Frame(self.frame)
        self.button_frame.pack(fill=tk.X)

        self.query_employee_button = tk.Button(self.button_frame, text="Query All Employees", command=self.query_all_employees)
        self.query_employee_button.pack(side=tk.LEFT, padx=5, pady=5)

        self.query_good_button = tk.Button(self.button_frame, text="Query All Goods and Show Pie Chart", command=self.query_all_goods)
        self.query_good_button.pack(side=tk.LEFT, padx=5, pady=5)

        self.query_purchase_button = tk.Button(self.button_frame, text="Query All Purchases and Show Line Chart", command=self.query_all_purchases)
        self.query_purchase_button.pack(side=tk.LEFT, padx=5, pady=5)

        self.tree_frame = tk.Frame(self.frame)
        self.tree_frame.pack(fill=tk.BOTH, expand=True)

        self.tree_scrollbar = tk.Scrollbar(self.tree_frame, orient=tk.VERTICAL)
        self.tree_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

        self.tree = ttk.Treeview(self.tree_frame, yscrollcommand=self.tree_scrollbar.set)
        self.tree.pack(fill=tk.BOTH, expand=True)
        self.tree_scrollbar.config(command=self.tree.yview)

    def query_all_employees(self):
        self.clear_tree()
        self.clear_chart()
        self.tree["columns"] = ("Eid", "EName", "EPas", "Elevel", "EtelPhone", "ESalary", "other")
        self.tree.column("#0", width=0, stretch=tk.NO)  # Hide the first empty column
        for col in self.tree["columns"]:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100, stretch=tk.NO)

        r = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True)
        employees = r.hgetall('tb_employee')
        for eid, employee in employees.items():
            employee_data = eval(employee)
            self.tree.insert("", "end", values=(eid, employee_data['EName'], employee_data['EPas'], employee_data['Elevel'], employee_data['EtelPhone'], employee_data['ESalary'], employee_data['other']))

    def query_all_goods(self):
        self.clear_tree()
        self.clear_chart()
        self.tree["columns"] = ("Gid", "GName", "GPay", "Cid", "GIntroduction", "other")
        self.tree.column("#0", width=0, stretch=tk.NO)  # Hide the first empty column
        for col in self.tree["columns"]:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100, stretch=tk.NO)

        r = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True)
        goods = r.hgetall('tb_good')
        customer_goods_count = {}
        for gid, good in goods.items():
            good_data = eval(good)
            self.tree.insert("", "end", values=(gid, good_data['GName'], good_data['GPay'], good_data['Cid'], good_data['GIntroduction'], good_data['other']))
            customer_goods_count[good_data['Cid']] = customer_goods_count.get(good_data['Cid'], 0) + 1

        self.show_pie_chart(customer_goods_count)

    def query_all_purchases(self):
        self.clear_tree()
        self.clear_chart()
        self.tree["columns"] = ("Pid", "Eid", "Pcount", "Ptotal", "Pdate", "other")
        self.tree.column("#0", width=0, stretch=tk.NO)  # Hide the first empty column
        for col in self.tree["columns"]:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100, stretch=tk.NO)

        r = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True)
        purchases = r.hgetall('tb_pay_main')
        monthly_totals = {}

        for pid, purchase in purchases.items():
            purchase_data = eval(purchase)
            self.tree.insert("", "end", values=(pid, purchase_data['Eid'], purchase_data['Pcount'], purchase_data['Ptotal'], purchase_data['Pdate'], purchase_data['other']))
            month = purchase_data['Pdate'][:6]
            monthly_totals[month] = monthly_totals.get(month, 0) + float(purchase_data['Ptotal'])

        self.show_line_chart(monthly_totals)

    def clear_tree(self):
        self.tree.delete(*self.tree.get_children())

    def clear_chart(self):
        if self.canvas:
            self.canvas.get_tk_widget().destroy()
            self.canvas = None

    def show_pie_chart(self, data):
        labels = list(data.keys())
        sizes = list(data.values())

        fig, ax = plt.subplots()
        ax.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=90)
        ax.axis('equal')

        self.display_chart(fig)

    def show_line_chart(self, data):
        months = sorted(data.keys())
        totals = [data[month] for month in months]

        fig, ax = plt.subplots()
        ax.plot(months, totals, marker='o')
        ax.set(xlabel='Month', ylabel='Total Price', title='Monthly Purchase Total')
        ax.grid()

        self.display_chart(fig)

    def display_chart(self, fig):
        self.clear_chart()  # Clear any existing chart before displaying a new one
        self.canvas = FigureCanvasTkAgg(fig, master=self.frame)
        self.canvas.draw()
        self.canvas.get_tk_widget().pack(fill=tk.BOTH, expand=True)

if __name__ == "__main__":
    root = tk.Tk()
    app = StatisticsQuery(root)
    root.mainloop()
